Flyway DB Migration and Versioning

Flyway is a simple but powerful tool that helps you to maintain your database properly.
The database for the Orchestra Solution Hub should be installed with Flyway since this approach gives the most flexibility.

Main reasons for using Flyway are:

• Database Schema versioning
• Consistent database migration
• Easy maintenance of database

Flyway has a command line tool that provides commands such as:

• migrate
• clean
• info
• validate
• baseline
• repair

Flyway maintains one table — flyway_schema_history — to achieve its tasks. This table will exist with other tables that come with Orchestra.
For more details, please have a look at https://flywaydb.org.

:::info Important: Flyway is only usable for databases that are created from scratch with Flyway. That means you cannot migrate existing databases that were created with SQL scripts or other tools. :::

---

Database Migration

Please have in mind that you need to create your user in the DB manually.
With Flyway, we do not want to maintain system scripts like in MySQL, MSSQL, Oracle, PostgreSQL, or MariaDB, but rather deal only with Schema and, optionally, a small set of data (like e.g. adding an admin user).

For migrating and maintaining the database, we have the following options:

• Migrate with Flyway – This option can be used for a newly created database or an existing database that was migrated by Flyway.
• Baseline and migrate with Flyway – This option is used for an existing database that was created without using Flyway.

---

Configuring Flyway

The following steps make your Flyway ready for use:

1. Create or edit the configuration file conf/flyway.toml, where you have to set the right database connection string, the location of the scripts, and optionally other parameters
   (more details: Flyway Commandline Migrate)

2. SQL scripts are by default located in the sql directory. The location of the scripts can be set in the flyway.toml.

Sample flyway.toml:

# Environment declaration, you can create more environments as needed
[environments.sample]
# URL of the database to be migrated
url = "jdbc:mariadb://localhost:3306/SOLUTIONHUB"
# User used to connect to database
user = "SOLUTIONHUB"
# Password used to connect to database
password = "SOLUTIONHUB"

# Flyway options
[flyway]
# Relative path of the location of SQL scripts
locations = ["filesystem:sql/MariaDB"]
# Environment to be used for migration
environment = "sample"
# Use this option if the clean command is needed
# cleanDisabled = false

warning

If the URL contains backslash (), it needed to escape by additional backslash. e.g. url = "jdbc:sqlserver://localhost\NAMEDINSTANCE;sendStringParametersAsUnicode=false;DatabaseName=SOLUTIONHUB;trustServerCertificate=true;"

• JDBC driver - your JDBC driver is located in the drivers directory.

Here you can see a typical Flyway directory with files and subdirectories:

Migrate

Migration is done using the command line tool:

flyway migrate

All SQL scripts that have a version number bigger than the current version of your DB Schema (from table flyway_schema_history) will be executed.

Baseline and Migrate

In this option, you first have to find your current version of the Orchestra DB - that is the version of Orchestra Solution Hub you are using. When you find out which version that is (e.g., V4.5.5.0), then please find the matching version of the SQL script(s) (for the given example that would be V75__V4.5.5.0_upgrade.sql).

Then you have to make a baseline for your database Schema:

flyway -baselineVersion=75 -baselineDescription="Base version up to orchestra V4.5.5.0" baseline

This command will create a table flyway_schema_history and set the baseline version in it.

After setting the baseline, the migration is done also with:

flyway migrate

All SQL scripts that have a version number bigger than the baseline version of your database Schema will be executed.